package com.hexb.smh.utils;

import com.hexb.core.enums.AscColors;
import com.hexb.core.utils.ObjectUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Function;

import static org.apache.poi.ss.usermodel.CellStyle.BORDER_THIN;

/**
 * @author : hexb
 */
@Slf4j
public class POIExcelHelper {

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    private static final DecimalFormat formatter = new DecimalFormat("#.#");

    /**
     * 从文件获取Workbook
     */
    public static Workbook getWorkBook(InputStream is, String extension) throws IOException {
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            if (extension.endsWith(XLS)) {
                workbook = new HSSFWorkbook(is);
            } else if (extension.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            log.error("Read Excel Error!", e);
            throw e;
        }
        return workbook;
    }

    public static <T> List<T> read(InputStream is,
                                   Function<Row, T> rowReader,
                                   String extension) throws IOException {
        return read(is, rowReader, extension, 0, 1, 1, null);
    }

    public static <T> List<T> read(InputStream is,
                                   Function<Row, T> rowReader,
                                   String extension,
                                   Integer startSheetIndex,
                                   Integer endSheetIndex,
                                   Integer startRowIndex,
                                   Integer endRowIndex) {
        List<T> list = new ArrayList<>();
        try (Workbook workBook = getWorkBook(is, extension)) {
            int ss = startSheetIndex == null ? 0 : startSheetIndex;
            int es = endSheetIndex == null ? workBook.getNumberOfSheets() : endSheetIndex;

            for (int s = ss; s < es; s++) {
                Sheet sheet = workBook.getSheetAt(s);
                int sr = startRowIndex == null ? 0 : startRowIndex;
                int er = endRowIndex == null ? sheet.getPhysicalNumberOfRows() : endRowIndex;
                for (int r = sr; r < er; r++) {
                    list.add(rowReader.apply(sheet.getRow(r)));
                }
            }
            return list;
        } catch (IOException e) {
            log.error("Read Excel Error!", e);
        }
        return list;
    }

    public static BigDecimal getCellValueBigDecimal(Cell cell) {
        String v = getCellValue(cell);
        return ObjectUtils.safeCall(() -> new BigDecimal(v), null);
    }

    /**
     * 读取Cell值
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            cell.setCellValue(formatter.format(cell.getNumericCellValue()));
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = formatter.format(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


    static public void rangeBorder(CellRangeAddress cellRange, HSSFSheet sheet, HSSFWorkbook workbook) {
        RegionUtil.setBorderTop(1, cellRange, sheet, workbook);
        RegionUtil.setBorderBottom(1, cellRange, sheet, workbook);
        RegionUtil.setBorderLeft(1, cellRange, sheet, workbook);
        RegionUtil.setBorderRight(1, cellRange, sheet, workbook);
        short color = HSSFColor.BLACK.index;
        RegionUtil.setBottomBorderColor(color, cellRange, sheet, workbook);
        RegionUtil.setTopBorderColor(color, cellRange, sheet, workbook);
        RegionUtil.setLeftBorderColor(color, cellRange, sheet, workbook);
        RegionUtil.setRightBorderColor(color, cellRange, sheet, workbook);
    }

    static public HSSFCellStyle headerStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = style(workbook);
        style.setFillForegroundColor(HSSFColor.SEA_GREEN.index);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        return style;
    }

    static public HSSFCellStyle style(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setBorderBottom(BORDER_THIN);
        style.setBorderLeft(BORDER_THIN);
        style.setBorderRight(BORDER_THIN);
        style.setBorderTop(BORDER_THIN);
        short color = HSSFColor.BLACK.index;
        style.setBottomBorderColor(color);
        style.setTopBorderColor(color);
        style.setLeftBorderColor(color);
        style.setRightBorderColor(color);
        return style;
    }
}
